Skip Headers

Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 5 Oracle Net

Oracle Net is an Oracle product providing distributed database and processing capabilities. Generally, Oracle Net for AS/400 supports network communications between Oracle applications, Oracle servers, and Oracle gateways across different AS/400 systems or foreign operating systems. For product-specific information, refer to the Oracle Database Net Services Administrator's Guide and the Oracle Database Net Services Reference.

This chapter presents information about the Oracle Net architecture and how to configure and use Oracle Net for AS/400. It contains the following sections:

- Overview of Oracle Net

- Connecting to the AS/400 through TCP/IP

- Checklists for Configuring Oracle Net

- Configuring Oracle Net for TCP/IP AS/400

- TCP/IP Connection Problems

- Resolving Connection Problems

- Troubleshooting TCP/IP Configuration

## 5.1 Overview of Oracle Net

Oracle Net is a required Oracle product supporting network communications between Oracle applications, Oracle servers, and Oracle gateways across different CPUs and operating systems. It also supports communication across different Oracle Databases and CPUs providing distributed database and distributed processing capabilities.

Oracle Net also allows applications to connect to multiple Oracle servers or gateways across a network, selecting from a variety of communications protocols and application program interfaces (APIs) to establish a distributed processing and distributed database environment.

A communications protocol is a set of implemented standards or rules governing data transmission across a network. An API is a set of subroutines providing a programming interface for application processes to the network environment.

### 5.1.1 Distributed Processing

Dividing processing between a front-end computer that is running an application and a back-end computer that is used by the application is known as distributed processing. Oracle Net enables an Oracle tool or application to connect to a remote computer containing an Oracle server or Oracle gateway.

### 5.1.2 Distributed Database

Several databases that are linked through a network and that appear as a single logical database are known as a distributed database. An Oracle tool running on a client computer or on an Oracle server running on a host computer can share and obtain information that is retrieved from other remote Oracle servers or Oracle gateways. Regardless of the number of database information sources, you might be aware of only one logical database.

### 5.1.3 Terminology for Oracle Net

The following terms are used to explain the architecture of Oracle Net for AS/400:

host: is the computer on which the database resides and the computer that runs the Oracle gateway. On AS/400, only a gateway can be running. The Oracle Database does not run on the AS/400.

client (task): is the application using an Oracle Net driver to communicate with the Oracle Database server or gateway. A server is also considered to be a client if it initiates a connection with another Oracle Database server, or with an Oracle gateway.

protocol: is a set of standards or rules governing the operation of a communication link.

driver: is the part of Oracle Net that supports a given network protocol or communication method.

network: is a configuration of devices and software that are connected in order to interchange information.

### 5.1.4 Oracle Net for AS/400 Architecture

Oracle Net connections are established on the AS/400 through a listener. A listener receives incoming connections from Oracle Net clients and starts or transfers to a job on the AS/400 system.

On the AS/400, the Oracle Net TNS listener is used for TCP/IP.

Figure 5-1 shows communication between a client and the AS/400. Oracle Net on the AS/400 can accept TCP/IP connections.

Figure 5-1 Oracle Net Communication to the OS/400

After the listener accepts the client connection, a batch job is started on the AS/400, or (more usually) a prestarted AS/400 batch job receives control. The batch job executes the gateway and sends a response back to the client. The response is data or a message. Each batch job is responsible for a client connection. Only one batch job is executed for a single connection, and a single connection is associated with only one batch job.

To reduce the waiting time for connecting to gateway jobs, a set of prestarted gateway batch jobs are associated with a TNS listener. The number of prestarted jobs and the point at which additional jobs are started can be modified by the system administrator.

### 5.1.5 File Name Structure

Oracle Net refers to files in the following format:

    basename.extension

where `basename` is the base portion of the name, and `extension` is the second part of the name.

An example of this form is `SQLNET.ORA`.

On the AS/400, the Oracle Net parameter files are installed in the gateway instance library using the following file name mapping:

- The base portion of the file name is mapped to a member name in an AS/400 file.

- The extension portion of the file name is mapped to an AS/400 file name.

For example, the `SQLNET.ORA` parameter file is mapped to member `SQLNET` in the ORA file that is located in the gateway instance library. Member names are referred to as:

    file(member_name)

Some of the parameter values in `ORA(SQLNET)` can be changed with the `CHGORANET` command. For more information, refer to "CHGORANET, Change Network Parameters".

For a list of the network files and members that are provided with the gateway, refer to Appendix A, "Oracle Net Files and Members".

#### 5.1.5.1 Trace Files for Job Members

To enable tracing for the listener or server, use the `CHGORANET` command to change the values of the `Listener trace level` and `Server trace level` parameters. Possible trace level values are:

- `*OFF` (the default) provides no trace information. Tracing is not enabled.

- `*USER` provides the least detailed trace data.

- `*ADMIN` provides more detailed trace data.

- `16` provides comprehensive trace data.

  Note: Follow the advice of Oracle Support Services for setting trace levels.

Because trace files are large, you should use the least detailed trace level possible. For more information about changing the trace level parameters, refer to "CHGORANET, Change Network Parameters".

When tracing is enabled, each job produces a trace file member in the TRC file with a member name in the following format:

`SERV` `xxxxxx` (for server jobs) or `LIST` `xxxxxx` (for listener jobs), where `xxxxxx` is the job number.

## 5.2 Connecting to the AS/400 through TCP/IP

Perform the following steps to configure your Oracle Database server computers for TCP/IP. Refer to the networking documentation for your Oracle Database server computer for more information about configuring it.

1. Step 1: Add a TCP/IP Connect Descriptor to tnsnames.ora

2. Step 2: Specify SID Names

### 5.2.1 Step 1: Add a TCP/IP Connect Descriptor to tnsnames.ora

Use the TCP/IP connect descriptor in the `tnsnames.ora` file on the Oracle Database 10g server:

    connect_name = (DESCRIPTION=
                    (ADDRESS=(PROTOCOL=TCP)
                      (PORT=1521)
                      (HOST=host_name)
                    )
                    (CONNECT_DATA=(SID=csi_name))
                  (HS=)
                  )

where:

`connect_name` is the name of the connection. This name must be unique within the tnsnames.ora file. The `connect_name` corresponds to the value that is found with the `USING` keyword on a `CREATE DATABASE LINK` command.

`TCP` is the TCP protocol that is used for TCP/IP connections.

`1521` is the default port number that is used by the Oracle Net listener on the AS/400. Change this value if you have changed the listener port number.

`host_name` is the TCP/IP host name of your AS/400 computer. It may be a DNS name, such as myas400.us.mycompany.com or an IP dotted value such as 10.2.3.7.

`csi_name` is the name of the communication side information (`*CSI`) file that is used by the listener to start the server. Usually, the `csi_name` is `ORAGATE`. Refer to "Step 2: Specify SID Names" for more SID names.

`HS=` This keyword is mandatory. The right-hand side of the HS keyword is not needed.

### 5.2.2 Step 2: Specify SID Names

Oracle servers that are using TCP/IP to connect to a gateway must specify a system identifier (SID) name in the `TNSNAMES.ORA` file that is used to connect to the AS/400. The SID name is used on the AS/400 to start the server. The listener uses the SID name to reference a `*CSI` file. The `*CSI` file uses a `TP_NAME` to start the required CL program in the gateway server. Several SID names are configured in the shipped system and are listed in the following table.

Set the SID to `ORAGATE` in order to use a prestarted job. `ORAGATE` would give the best response time. Setting the SID to other values should be done in special situations as directed by Oracle support personnel.

Table 5-1 SID Names That Are configured in the Shipped System

SID | *CSI | TP_NAME | CL Program
--- | --- | --- | ---
`ORAGATE` | `ORAGATE` | `ORAGWTPJ` | `RUNORAGTTP`
`ORAGTPJ` | `ORAGTPJ` | `ORAGWTPJ` | `RUNORAGTTP`
`ORAGTEJ` | `ORAGTEJ` | `ORAGWTEJ` | `RUNORAGTTE`
`ORAGTTC` | `ORAGTTC` | `ORAGWTTC` | `RUNORAGTTT`

The following table provides the name and usage of each SID:

Table 5-2 SID Names and Their Usage

SID | Usage
--- | ---
`ORAGATE` | uses one of the prestarted TCP/IP jobs.
`ORAGTPJ` | is an alias for `ORAGATE`.
`ORAGTEJ` | starts a new gateway job.
`ORAGTTC` | starts a gateway job and a CPIC trace.It is also used for debugging the connection between the listener and the server.

Refer to "Viewing Jobs" for instructions on viewing jobs and a description of the jobs that typically exist.

## 5.3 Checklists for Configuring Oracle Net

The following sections include checklists for configuring Oracle Net and for connecting to an Oracle server.

### 5.3.1 Checklist for Configuring Oracle Net for TCP/IP

- Step 1: Configure the Port Number

- Step 2: Define the Host Name

- Step 3: Verify the Host Name

- Step 4: Verify that the Listener is Started

### 5.3.2 Connecting Through TCP/IP (on the Oracle Server) Checklist

- Step 1: Add a TCP/IP Connect Descriptor to tnsnames.ora

- Step 2: Specify SID Names

### 5.3.3 Preliminary Step: Define a Physical Line

This step is required only if no physical connection currently exists between the Oracle Database 10g server and the AS/400.

To define the physical connection, use the `CRTLINETH` command to create an ethernet connection.

When you use the `CRTLINETH` command, you can set the `AUTOCREATE` `CONTROLLER` parameter to `*YES` (the AS/400 system default is `*NO`). This enables the AS/400 auto configuration feature.

If a line is already defined, then you can use the `DSPLIND` `line_name` command to display the line description parameters.

These line description parameters are used to configure the network on the computer where the gateway resides.

## 5.4 Configuring Oracle Net for TCP/IP AS/400

When you install the gateway, you are prompted for a listener port, through which TCP/IP connections will be established. By default, a listener job starts when the gateway subsystem starts. The listener monitors the TCP/IP port that you specified and accepts incoming connections directed to that port. After a connection is made, the listener transfers control to a server job by using the Oracle Net bequeath mechanism.

Internal process communication (IPC) between the listener and the server job is achieved through AS/400 local APPC devices. The listener then redirects the client connection to a randomly generated port that is assigned to the server job. This process is the Oracle Net inherit mechanism.

For more information about Oracle Net, refer to the Oracle Database Net Services Administrator's Guide and the Oracle Database Net Services Reference.

Figure 5-2 demonstrates a TCP/IP connection flow.

Figure 5-2 TCP/IP Connection Flow

Note: You may need to refer to the IBM Information Center for information about TCP/IP concepts and terminology, setting up TCP/IP, and using TCP/IP. Search for "tcp/ip setup" in the Information Center. Correct configuration of TCP/IP on the AS/400 ensures that the gateway functions properly.

### 5.4.1 Configuring for TCP/IP

Perform the following steps to configure your TCP/IP connection between the Oracle Database 10g server and the AS/400:

  Step 1: Configure the Port Number
  Step 2: Define the Host Name
  Step 3: Verify the Host Name
  Step 4: Verify that the Listener is Started

### 5.4.2 Step 1: Configure the Port Number

If the gateway is already assigned to port 1521 or to another available port number, then go to "Step 2: Define the Host Name".

The gateway listener must be assigned a dedicated port number on which to listen for incoming connection requests. The default port number is 1521. This number can be changed during installation if you know that port 1521 is already used or if you prefer to use a different port number for any other reason. The port number may also be changed after installation by using the `CHGORANET` command.

You can find the values of port numbers that are currently active by using the AS/400 command `NETSTAT *CNN`. Each running instance requires a unique listener port number. Two gateway instances that are active at the same time cannot use the same listener port number. For more information, refer to "CHGORANET, Change Network Parameters".

### 5.4.3 Step 2: Define the Host Name

To define the host name, perform the following steps:

1. Run the AS/400 `CFGTCP` command.

2. Select option 12, Change TCP/IP domain information. The panel in Example 5-1, "Change TCP/IP Domain (CHGTCPDMN)" is displayed:

Example 5-1 Change TCP/IP Domain (CHGTCPDMN)

    ___________________________________________________________________________________________________
                               Change TCP/IP Domain (CHGTCPDMN)

             Type Choices, press Enter.

             Host name...                       'AS400A'
             Domain name...                     'US.ORACLE.COM'
             Domain search list...              *DFT
             Host name search priority...       *REMOTE   *REMOTE,  *LOCAL,  *SAME
             Domain name server
               Internet address...              '140.24.88.144'
                                                '140.24.244.29'

                                                                             Bottom

             F3=Exit  F4=Prompt  F5=Refresh  F10=Additional parameters  F12=Cancel
             F11=How to use this dispay  F24=More Keys

    ___________________________________________________________________________________________________

1. Enter your domain name if the Domain name field is empty. Enter your host name if the Host name field is empty.

   Press Enter to save your changes and return to the option list panel. (The changes take effect after you restart the gateway.)

   The host name and the domain name combine to form the qualified host name. For example, AS400A combines with US.ORACLE.COM to form AS400A.US.ORACLE.COM as the qualified host name.

### 5.4.4 Step 3: Verify the Host Name

Verify that your host name is in your host name table by using the following AS/400 command:

    PING host_name.domain_name

You can also use a `PING` command on the client-side Oracle Database server to verify that it can communicate with the OS/400.

Note: The `PING LOOPBACK` command does not verify the host name.

### 5.4.5 Step 4: Verify that the Listener is Started

Use the Oracle Gateway Monitor to verify that the listener is started. If it is necessary to start the listener, then use the Oracle Gateway Monitor to do so, or start the listener manually with the following command:

    STRORALSN

You can use the `NETSTAT *CNN` command to verify that the port that is assigned to the `LISTENER` is, in fact, being used by the `LISTENER` job in a given gateway instance. For more information about using the Oracle Gateway Monitor, refer to Chapter 8, "Administering the Gateway".

## 5.5 TCP/IP Connection Problems

If you cannot access AS/400 data by using Oracle Net after configuring TCP/IP, then check to see whether the AS/400 is reachable by pinging the AS/400 from an Oracle Database server host from the operating system prompt, or use a similar command from the shell prompt:

    PING host_name.domain_name

where `host_name` is the name that identifies the AS/400.

If you cannot resolve your connection problem, then refer to "Message and Error Code Processing" for more information.

## 5.6 Resolving Connection Problems

You may experience the following error types: AS/400 data is not reachable, errors ORA-28509, ORA-28511, ORA-28500, ORA-12154, or a hanging condition.

### 5.6.1 AS/400 Connection Problems

The following suggestions may resolve your connection problems:

#### 5.6.1.1 The AS/400 is not reachable:

Review your Oracle Net configuration.

#### 5.6.1.2 The AS/400 is reachable, but you cannot access AS/400 data:

Verify that the definition for the host name (from "Step 2: Define the Host Name" in the "Configuring for TCP/IP" section) contains the fully qualified name. Without a fully qualified `host_name.domain_name,` Oracle Net cannot resolve the address, and you will be unable to access AS/400 data.

### 5.6.2 ORA-28509

The following are some causes of the error.

#### 5.6.2.1 `HS=` is missing from your tnsnames.ora file:

`HS=` is a parameter that you must specify in the tnsnames.ora file, or you will not be able to use version 10 of the gateway. The correct syntax is shown at "Connecting to the AS/400 through TCP/IP". If `HS=` is missing from the entry in your tnsnames.ora file, then some symptoms to look for are a hanging connection and errors ORA-2068 and ORA-3114. Search Metalink for Note 136294.1, which provides more details about this topic.

#### 5.6.2.2 `HS=` Not in Correct Place:

`HS=` is specified in tnsnames.ora, but it is not specified in the correct place, or the number of parentheses is uneven (an odd number due to missing or extra parentheses).

`HS=` needs to be set up outside the `CONNECT_DATA` specification. If you misplace `HS=,` or if you do not have the correct parenthesis specification, then you can get error ORA-28509.

Other symptoms (same as when `HS=` is missing from your tnsnames.ora file) are hanging connection and errors ORA-2068 and ORA-3114.

#### 5.6.2.3 TCP/IP Configuration:

TCP/IP configuration under AS/400 ("Option 10 of CFGTCP: Work with TCP/IP Host Table Entries") is not configured as anticipated by the gateway listener.

The gateway listener requires `host_name.domain_name` in the TCP/IP host table entries. If you do not specify this, then you will not be able to get any response back from the gateway. You will be able to see the connection in the AS/400, but no data can be transferred.

#### 5.6.2.4 Gateway Listener is Not Up:

By default, the gateway listener is automatically started when you start the gateway instance. You can manually start the listener by executing the command: `STRORALSN`.

### 5.6.3 ORA-28511

The following are some causes of the error.

#### 5.6.3.1 Gateway or Listener is Not Up:

Check to see that the gateway is up and running correctly and is properly configured. Check to see if the listener is up.

#### 5.6.3.2 Set Fully Qualified `host.domain` Name

Ensure that a fully qualified `host.domain` name has been set on the AS/400. Use `CFGTCP` to do this. Use Option 10 and Option 12 to set a fully qualified `host.domain` name. Use ping to verify that the `host.domain` name matches the IP address of the host name.

### 5.6.4 ORA-28500

Check any accompanying message from the AS/400 to determine what the problem might be, or check the AS/400 job log (`RUNORAGTTP` job log). For example, message SQL-7008 indicates that the file is not journaled.

### 5.6.5 ORA-12154

This error indicates that a missing or incorrect tnsnames file has been specified, or that an incorrect spelling or service name has been specified in the tnsnames file. This is generally an Oracle Net issue, not a gateway issue.

## 5.7 Troubleshooting TCP/IP Configuration

You may encounter errors during the test phase of the gateway installation even though you are able to issue a `PING`. If you do encounter such errors, then please check the panels that were used to define the host name. Specifically, check the values presented when using option 12 of the `CFGTCP` command.

### 5.7.1 Option 10 of CFGTCP: Work with TCP/IP Host Table Entries

Table 5-3 TCP/IP Host Table Entries

Option | Internet Address | Host Name
--- | --- | ---
| 10.10.5.40 | AS400 AS400.US.ORACLE.COM
| 127.0.0.1 | LOOPBACK LOCALHOST

Enter Internet addresses and host names. Host names in the "Work with TCP/IP Host Table Entries" panel must include both the unqualified and the qualified names (the one that includes the domain name).

Option 10 depends upon Option 12. Option 12 defines your host name as well as your domain name. Under Option 10, you must enter your host name and your `hostname.domain_name`. This may seem redundant, but if the gateway is not configured correctly for TCP/IP, then the gateway will not make a connection, regardless of the output from the `PING` command. Any changes to `CFGTCP` will require you to restart the gateway.

TCP configuration is documented in "Configuring Oracle Net for TCP/IP AS/400" in this chapter.

### 5.7.2 Option 12 of CFGTCP: Change TCP/IP Domain (CHGTCPDMN)

Table 5-4 Change TCP/IP Domain

Option | Host name | Domain name
--- | --- | ---
| `'AS400A'` | '`US.ORACLE.COM'`

The host name and the domain name combine to form the qualified host name. For example, `AS400A` (as illustrated in Option 12) combines with "US.ORACLE.COM" to form the qualified host name AS400A.US.ORACLE.COM.